package com.edu.dao;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BaseDao {
    private static String url = "jdbc:mysql://127.0.0.1:3306/books?useServerPrepStmts = true&useSSL=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";;
    private static String user = "root";
    private static String password = "123456";
    private static Connection connection;
    private static PreparedStatement preparedStatement;
    private static ResultSet resultSet;
//    static {
//    	//读取配置文件
//    	Properties prop = new Properties();
//    	FileInputStream fis;
//		try {
//			fis = new FileInputStream("com//edu//resources//database.properties");
//			prop.load(fis);
//			System.out.println(prop);
//			fis.close();
//		} catch (FileNotFoundException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		url = (String)prop.get("url");
//		user = (String)prop.get("username");
//		password = (String)prop.get("password");
//		System.out.println(url);
//    }
    //通过反射可以获取到对应类字节码文件的对象
    public static<T> List<T> executeQuaryList(String sql,Object[] params,Class<T> clazz){
        List<T> list = new ArrayList<>();
        Connection myConnection = getConnection();
        try {
            preparedStatement = myConnection.prepareStatement(sql);//预编译sql
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();//获取参数元数据对象
            int count = parameterMetaData.getParameterCount();//获取参加预编译参数的个数
            
            //填充占位符

            if (params != null){
                for (int i = 0; i < count; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            System.out.println(preparedStatement);
            //处理结果集
            resultSet = preparedStatement.executeQuery();
            //获取结果集对象
            ResultSetMetaData metaData = preparedStatement.getMetaData();
            while (resultSet.next()){
                Constructor<T> conn = null;//获取对应类的构造方法的对象
                T t = null;
                try {
                    conn = clazz.getConstructor();
                } catch (NoSuchMethodException e) {
                    throw new RuntimeException(e);
                }
                try {
                    t = conn.newInstance();//创建对应类的对象
                } catch (InstantiationException | IllegalAccessException | InvocationTargetException e) {
                    throw new RuntimeException(e);
                }
                for (int i = 0;i<metaData.getColumnCount();i++){
                    String columnName = metaData.getColumnName(i + 1);//获取列名
                    Object value = resultSet.getObject(columnName);//获取列值
                    Field[] declaredFields = clazz.getDeclaredFields();//获取所有成员变量
                    for (int j = 0; j < declaredFields.length; j++) {
                        Field declaredField = declaredFields[j];//获取每一个成员变量对象
                        if (columnName.equals(declaredField.getName())){
                            Class<?> type = declaredField.getType();//获取成员变量的参数类型
                            String fileName = declaredFields[j].getName();
                            String result = "set"+fileName.substring(0,1).toUpperCase()+fileName.substring(1);
                            try {
                                Method method = clazz.getMethod(result,type);//获取指定成员方法
                                if (value == null){
                                    continue;
                                }                           
                                method.invoke(t,value);//执行成员方法
                            } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                                throw new RuntimeException(e);
                            }
                        }
                    }
                }
                list.add(t);
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }
    }
    public static int executeQuaryCount(String sql) {
    	//获取连接
    	 Connection myConnection = getConnection();
    	 int count = -1;
    	 try {
    		//预编译sql
			preparedStatement = myConnection.prepareStatement(sql);
			//处理结果集
            resultSet = preparedStatement.executeQuery();
           
            if(resultSet.next()) {
            	count = resultSet.getInt(1);
            	return count;
            }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}//预编译sql
    	 finally {
    		 try {
				resultSet.close();
				preparedStatement.close();
	            connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
    	 }
    	return count;
    }
    public static<T> T executeQuaryOne(String sql,Object[] params,Class<T> clazz){
    	T t = null;
        Connection myConnection = getConnection();
        try {
            preparedStatement = myConnection.prepareStatement(sql);//预编译sql
            ParameterMetaData parameterMetaData = preparedStatement.getParameterMetaData();//获取参数元数据对象
            int count = parameterMetaData.getParameterCount();//获取参加预编译参数的个数
            //填充占位符
            if (params != null){
                for (int i = 0; i < count; i++) {
                    preparedStatement.setObject(i+1,params[i]);
                }
            }
            //处理结果集
            resultSet = preparedStatement.executeQuery();
            //获取结果集对象
            ResultSetMetaData metaData = preparedStatement.getMetaData();
            while (resultSet.next()){
                Constructor<T> conn = null;//获取对应类的构造方法的对象
                try {
                    conn = clazz.getConstructor();
                } catch (NoSuchMethodException e) {
                    throw new RuntimeException(e);
                }
                try {
                    t = conn.newInstance();//创建对应类的对象
                } catch (InstantiationException | IllegalAccessException | InvocationTargetException e) {
                    throw new RuntimeException(e);
                }
                for (int i = 0;i<metaData.getColumnCount();i++){
                    String columnName = metaData.getColumnName(i + 1);//获取列名
                    Object value = resultSet.getObject(columnName);//获取列值
                    Field[] declaredFields = clazz.getDeclaredFields();//获取所有成员变量
                    for (int j = 0; j < declaredFields.length; j++) {
                        Field declaredField = declaredFields[j];//获取每一个成员变量对象
                        if (columnName.equals(declaredField.getName())){
                            Class<?> type = declaredField.getType();//获取成员变量的参数类型
                            String fileName = declaredFields[j].getName();
                            String result = "set"+fileName.substring(0,1).toUpperCase()+fileName.substring(1);
                            try {
                                Method method = clazz.getMethod(result,type);//获取指定成员方法
                                if (value == null){
                                    continue;
                                }
                                method.invoke(t,value);//执行成员方法
                            } catch (NoSuchMethodException | IllegalAccessException | InvocationTargetException e) {
                                throw new RuntimeException(e);
                            }
                        }
                    }
                }
            }
            return t;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try {
                resultSet.close();
                preparedStatement.close();
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }
    }
    
    public static Connection getConnection(){
    	
    		try {
    			Class.forName("com.mysql.cj.jdbc.Driver");
    		} catch (ClassNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
            try {
                connection = DriverManager.getConnection(url,user,password);
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            return connection;
    	
    	
    }
    //登录
    //查询public User selectUser(String userName, String password)
   
    public static int executeUpdate(String sql, Object[] params){
        Connection myConnection = getConnection();
        try {
            preparedStatement = myConnection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
            System.out.println(preparedStatement);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            close(preparedStatement,connection);
        }
    }
    public static boolean executeUpdateByTranstion(String[] sqls,Object[][] params,Connection connection) throws SQLException{
        boolean result = false;
        for (int i = 0; i < sqls.length; i++) {        	
        	String sql = sqls[i];
        	preparedStatement = connection.prepareStatement(sql);
        	//取出对应sql的数组
        	Object[] param = params[i];
        	for (int j = 0; j < param.length; j++) {
                preparedStatement.setObject(j+1,param[j]);
            }
        	System.out.println(preparedStatement);
        	//执行sql
        	int count = preparedStatement.executeUpdate();
        	//每次执行sql关闭 preparedStatement
        	preparedStatement.close();
        	if(count == 1) {
        		result = true;
        	}else {
        		result =false;
        	}
		}
        //关流操作交给上层
        return result;
    }
    public static void close(PreparedStatement p,Connection c){
        try {
            p.close();
            c.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
